package com.bw.mapper;

import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {
    @Select("select hour(create_time) name,count(*)value\n" +
            "from payment_info group by hour(create_time) order by hour(create_time);")
    List<Vo1> cx1();

    @Select("select tqi.question_txt name,\n" +
            "count(t.question_id) a1,\n" +
            "sum(t.is_correct) a2,\n" +
            "sum(t.is_correct)/count(t.question_id)*100 a3\n" +
            "from test_exam_question t\n" +
            "left join test_question_info tqi on tqi.id = t.question_id\n" +
            "group by tqi.question_txt;")
    List<Vo2> cx2();

    @Select("select t.paper_title name,\n" +
            "count(distinct user_id) a1,\n" +
            "sum(score)/count(distinct user_id) a2,\n" +
            "sum(duration_sec)/count(distinct user_id) a3\n" +
            "from test_paper t\n" +
            "left join test_exam te on t.id = te.paper_id\n" +
            "group by t.paper_title;")
    List<Vo2> cx3();

    @Select("select  bs.source_site name,count(oi.id) value\n" +
            "from  order_info oi left join order_detail od on oi.id = od.order_id\n" +
            "left join course_info ci on od.course_id = ci.id\n" +
            "left join chapter_info c on ci.id = c.course_id\n" +
            "left join video_info vi on c.id = vi.chapter_id\n" +
            "left join base_source bs on vi.video_source_id = bs.id\n" +
            "group by bs.source_site;")
    List<Vo1> cx4();
}
